use master
GO
if exists (select * from sys.databases where name = 'OnlineRailway')
	drop database OnlineRailway
GO
Create database OnlineRailway
GO
use OnlineRailway
GO
Create Table [User]
(
	UserID 			char(10) primary key,
	UserName 		varchar(30),
	UserPhone 		varchar(12),
	UserMail 		varchar(max),
	UserAddress 	varchar(max),
	UserType 		int
)
INSERT INTO [User] VALUES ('G11', 'Pham Quang Hung', '01231123213', 'aaaa@yahoo.com', '12a-a-a-a', 1)
INSERT INTO [User] VALUES ('G12', 'Hoang Van Thai', '01456456456', 'bbbb@yahoo.com', '123sadaad', 1)
INSERT INTO [User] VALUES ('G13', 'Nguyen Hong Qui', '0127897893', 'cccc@yahoo.com', '12zxczxc', 4)
INSERT INTO [User] VALUES ('G14', 'Cao Van Tuyen', '01457645213', 'dddd@yahoo.com', '12dfaasf', 3)

GO
Create Table LoginDetails
(
	LoginID 		char(10) primary key,
	UserID			char(10),
	LoginName 		varchar(20),
	LoginPass 		varchar(10)
)
GO
Create Table Passenger
(
	PNRNumber 				char(10) primary key,
	PassengerName 			varchar(30),
	PassengerAge 			int,
	PassengerGender 		bit,
	PassengerTotal 			int,
	PassengerDateOfTravel 	datetime,
	PassengerClass 			varchar(20),
	TrainID 				char(10),
	FareID 					char(10)
)
GO
Create Table Train
(
	TrainID 				char(10) primary key,
	TrainName 				varchar(50),
	TrainType 				varchar(50),
	RouteID 				char(10),
	ScheduleID 				char(10), 
	TrainNoCompartment   	int, --So cho (con trong)
	Train1AC 				int, --So toa co cai nay
	Train2AC 				int,
	Train3AC 				int,
	TrainSleeper 			int, --So cho nam
	TrainGeneral 			int --Cung la mot loai toa
)

INSERT INTO  Train VALUES ('TR1','EXPRESS NORTH TO SOUTH','Express', 'ROU2', 'SCHE1', 5, 1, 2, 0, 2, 0)
INSERT INTO  Train VALUES ('TR2','SS101','Tourist', 'ROU1', 'SCHE4', 8, 0, 2, 4, 2, 0)
INSERT INTO  Train VALUES ('TR3','NKA3G','Express/Mail', 'ROU3', 'SCHE2', 0, 0, 0, 0, 0, 0)
INSERT INTO  Train VALUES ('TR4','ASS000','Special', 'ROU5', 'SCHE3', 10, 2, 2, 2, 2, 2)

GO
Create Table FareDetails --Loai ve
(
	FareID 				char(10) primary key,
	FareName 			varchar(20),
	FareType 			varchar(20),
	TrainID 			char(10),
	CompartmentType 	int, --Loai toa tau
	RouteID 			char(10),
	FeeID 				char(10)
)

INSERT INTO  FareDetails VALUES ('FA1','Express','One Way','TR1',1,'ROU2','FEE1')
INSERT INTO  FareDetails VALUES ('FA2','Common','Two Ways','TR2',2,'ROU1','FEE1')
INSERT INTO  FareDetails VALUES ('FA3','Common','Two Ways','TR2',1,'ROU1','FEE3')
INSERT INTO  FareDetails VALUES ('FA4','Express','One Way','TR4',1,'ROU5','FEE4')

GO
Create Table Fee --Le Phi
(
	FeeID 		char(10) primary key,
	FeeName 	varchar(20),
	FeeCost 	float
)

INSERT INTO  Fee VALUES ('FEE1','FareAC1Cost',12.00)
INSERT INTO  Fee VALUES ('FEE2','FareAC2Cost',13.25)
INSERT INTO  Fee VALUES ('FEE3','FareAC3Cost',15.50)
INSERT INTO  Fee VALUES ('FEE4','FareSleeperCost',18.00)
INSERT INTO  Fee VALUES ('FEE5','FareGeneralCost',19.98)
--INSERT INTO  Fee VALUES ('FEE6','FareReturn',12.21)

GO
Create Table Station --Nha ga
(
	StationID 		char(10) primary key,
	StationName 	varchar(30),
	RouteID 		char(10)
)

INSERT INTO  Station VALUES ('STA1','Thainguyen','ROU1')
INSERT INTO  Station VALUES ('STA2','Hue','ROU2')
INSERT INTO  Station VALUES ('STA3','Danang','ROU2')
INSERT INTO  Station VALUES ('STA4','WTF','ROU4')
INSERT INTO  Station VALUES ('STA5','DAFUQ','ROU5')

GO 
Create Table [Route]--Tuyen duong
(
	RouteID 		char(10) primary key,
	RouteStart 		varchar(30),
	RouteStop 		varchar(30),
	RouteDistance 	int
)

INSERT INTO  [Route] VALUES ('ROU1', 'Hanoi','Laocai', 550)
INSERT INTO  [Route] VALUES ('ROU2', 'Hanoi','HCMCity', 2300)
INSERT INTO  [Route] VALUES ('ROU3', 'Bacgiang','Thainguyen', 200)
INSERT INTO  [Route] VALUES ('ROU4', 'Hue','Lamdong', 1230)
INSERT INTO  [Route] VALUES ('ROU5', 'Vinhphuc','Tayninh', 400)



GO
Create Table Schedule --Lich Trinh
(
ScheduleID 		char(10) primary key,
ScheduleDate	varchar(20),
ScheduleTime	varchar(20)
)

INSERT INTO  Schedule VALUES ('SCHE1', '12/12/2012', '2h30 p.m')
INSERT INTO  Schedule VALUES ('SCHE2', '14/12/2012', '4h45 a.m')
INSERT INTO  Schedule VALUES ('SCHE3', '16/12/2012', '8h00 a.m')
INSERT INTO  Schedule VALUES ('SCHE4', '18/12/2012', '12h50 p.m')

GO
ALTER Table LoginDetails ADD CONSTRAINT fk1 FOREIGN KEY (UserID) REFERENCES [User] (UserID)
GO
ALTER Table Passenger ADD CONSTRAINT fk2 FOREIGN KEY (TrainID) REFERENCES Train (TrainID)
GO
ALTER Table Passenger ADD CONSTRAINT fk3 FOREIGN KEY (FareID) REFERENCES FareDetails (FareID)
GO
ALTER Table Train ADD CONSTRAINT fk4 FOREIGN KEY (RouteID) REFERENCES [Route] (RouteID)
GO
ALTER Table Train ADD CONSTRAINT fk5 FOREIGN KEY (ScheduleID) REFERENCES Schedule (ScheduleID)
GO
ALTER Table FareDetails ADD CONSTRAINT fk6 FOREIGN KEY (RouteID) REFERENCES [Route] (RouteID)
GO
ALTER Table FareDetails ADD CONSTRAINT fk7 FOREIGN KEY (FeeID) REFERENCES Fee (FeeID)
GO
ALTER Table Station ADD CONSTRAINT fk8 FOREIGN KEY (RouteID) REFERENCES [Route] (RouteID)


--SELECT *
--FROM Train 
--LEFT JOIN Schedule
--ON Train.ScheduleID = Schedule.ScheduleID
--LEFT JOIN [Route]
--ON Train.RouteID = [Route].RouteID

GO

CREATE PROC SearchRoute
@RouteStart 	varchar(30) = null,
@RouteStop 		varchar(30) = null
AS
BEGIN
SELECT * FROM [Route] WHERE @RouteStart = [Route].RouteStart AND @RouteStop = [Route].RouteStop
END

--go
--SearchRoute @RouteStart = Hanoi,
--			@RouteStop = HCMCity

GO

CREATE PROC SearchTrain
@RouteID 	char(10) = null
AS
BEGIN
SELECT * FROM Train LEFT JOIN [Route]
ON @RouteID = Train.RouteID AND @RouteID = [Route].RouteID
END

--go
--SearchTrain @RouteID = ROU2

GO

CREATE PROC SearchSchedule
@ScheduleID 	char(10) = null
AS
BEGIN
SELECT * FROM Train LEFT JOIN Schedule
ON @ScheduleID = Train.ScheduleID AND @ScheduleID = Schedule.ScheduleID
END

--go
--SearchSchedule @ScheduleID = SCHE1